from sqlalchemy import create_engine, text
from utils.read_config import read_config
import yaml
import pandas as pd
import logging

def read_config_database():
    database=read_config("database","database")
    host=database['host']
    port=database['port']
    name=database['name']
    user=database['user']
    password=database['password']
    # 数据库连接字符串
    DATABASE_URL = f"postgresql://{user}:{password}@{host}:{port}/{name}?client_encoding=utf8"
    return DATABASE_URL

def read_config_table(tablename):
    table=read_config("database","table")
    for config in table:    
        tablenames=config['tablename']
        columns=config['columns']
        if tablenames==tablename:
            columns_list = columns.split(",")
            # 使用列表推导式去掉每个元素的首尾空格
            cleaned_list = [col.strip() for col in columns_list]
            return cleaned_list

    raise Exception(f"表{tablename}未找到")
       
    
def sql_query():
    DATABASE_URL=read_config_database()
    # 创建数据库引擎
    engine = create_engine(DATABASE_URL)

    # 使用上下文管理器创建数据库连接
    with engine.connect() as connection:
        # 执行 SQL 查询
        result = connection.execute(text("SELECT * FROM usertable"))
        for row in result:
            print(row)

def sql_insert(data_to_insert,tableName):
    try:
        DATABASE_URL=read_config_database()
        columns=read_config_table(tableName)
    except Exception as e:
        logging.info(f"{e}")
        return
    # 创建数据库引擎
    engine = create_engine(DATABASE_URL)
    # 将数据转换为DataFrame
    df = pd.DataFrame(data_to_insert, columns=columns)
    # 使用with语句确保连接自动关闭
    try:
        with engine.connect() as connection:
            try:
                # 开始事务
                with connection.begin() as transaction:
                    # 将数据插入到数据库
                    df.to_sql(tableName, connection, if_exists='append', index=False)
                    logging.info("数据插入成功")
            except Exception as e:
                logging.info(f"数据插入失败: {e}")
                # 如果发生错误，回滚事务
                transaction.rollback()
    except Exception as e:
        logging.info(f"数据库连接出错{e}")

if __name__=='__main__':
    data_to_insert = [
    [3,"Alice", 20, "A"],
    [4,"Bob", 22, "B"],
    [5,"Charlie", 21, "A"]
    ]
    tableName='usertable'
    # sql_insert(data_to_insert,tableName)
    # try:
    #     columns=read_config_table('usertable')
    #     print(columns)
    # except Exception as e:
    #         print(f"数据插入失败: {e}")